package cn.itcast.goods.category.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


import cn.itcast.commons.CommonUtils;
import cn.itcast.goods.category.doamin.Category;
import cn.itcast.jdbc.TxQueryRunner;

public class CategoryDao {

	private QueryRunner qr=new TxQueryRunner();
	
	public List<Category> findAll() throws SQLException {
		//1获取所有的一级分类
		//pid为null就是一级分类		
		String sql="select * from t_category where pid is null order by orderBy";
		List<Category> parents= qr.query(sql, new BeanListHandler<Category>(Category.class));
		
	
		//2.遍历循环每一个一级分类，为其加载他的所有二级分类
		sql="select * from t_category where pid=? order by orderBy";
		for(Category parent:parents){
				
		  //获取当前一级分类的所有二级分类
			List<Category> children=qr.query(sql, new BeanListHandler<Category>(Category.class),parent.getCid());
			
		  //给当前一级分类设置二级分类
			parent.setChildren(children);
		  //为每个二级分类设置一级分类
			for(Category child:children){
				child.setParent(parent);
			}
		}
		//3.返回一级分类List，每一个一级分类中都包含了自己的二级分类
		return parents;
	}

	public void add(Category category) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into t_category(cid, cname, pid, classify) values (?,?,?,?) ";
		String pid = null;
		if(category.getParent() != null)
		{
			pid = category.getParent().getCid(); 
		}
		qr.update(sql, category.getCid(),category.getCname(),pid,category.getClassify());
	}

	/**
	 * 查询所有的一级分类
	 * @return
	 * @throws SQLException 
	 */
	public List<Category> findByParents() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from t_category where pid is null order by orderBy";
		return qr.query(sql, new BeanListHandler<Category>(Category.class));
	}

	
	/**
	 * 加载一级分类
	 * @param cid
	 * @return
	 * @throws SQLException 
	 */
	public Category load(String cid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from t_category where cid = ?";
		Map<String,Object> map = qr.query(sql, new MapHandler(), cid);
		Category category = CommonUtils.toBean(map, Category.class);
		Object pid = map.get("pid");
		if(pid != null)
		{
			Category parent = new Category();
			parent.setCid((String) pid);
			category.setParent(parent);
		}
		return category;
	}

	/**
	 * 编辑一级分类
	 * @param category
	 * @return
	 * @throws SQLException 
	 */
	public void edit(Category category) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "update t_category set cname = ? ,pid = ? ,classify = ? where cid = ? ";
		String pid = null;
		if(category.getParent() != null)
		{
			pid = category.getParent().getCid();
		}
		Object[] params = {category.getCname(),pid,category.getClassify(),category.getCid()};
		qr.update(sql, params);
	}

	/**
	 * 刪除一級分類
	 * @param cid
	 * @throws SQLException 
	 */
	public void delete(String cid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "delete from t_category where cid = ?";
		qr.update(sql, cid);
	}

	/**
	 * 查找一级分类下子分类的个数
	 * @param cid
	 * @return
	 * @throws SQLException 
	 */
	public int findChildrenCount(String cid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select count(1) from t_category where pid = ?";
		Number count = (Number) qr.query(sql, new ScalarHandler(), cid);
		
		return count == null ? 0 :count.intValue();
	}

	/**
	 * 查找以及分类下所有的二级分类
	 * @param pid
	 * @throws SQLException 
	 */
	public List<Category> findChildren(String pid) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from t_category where pid = ?";
		return qr.query(sql, new BeanListHandler<Category>(Category.class), pid);
	}

}
